﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;
using SGIMail.Models;
using System.IO;
using DocumentFormat.OpenXml.Validation;

namespace SGIMail.BL
{
    public class Spreadsheet
    {
        /// <summary>
        /// Reads through the file looking for data under certain "headers" which actually only is string values.
        /// The "headers" are mapped to variables that stores the column indexes and from that information retrieves the file's
        /// data by looping through the rows and cells and retrieves the data from the specified indexes and 
        /// places it in a Contact object.
        /// </summary>
        /// <param name="stream">The file as a stream.</param>
        /// <returns>List<Contact> where a contact object is a row.</returns>
        public List<Contact> Read(Stream stream)
        {
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, false))
            {
                WorkbookPart workbookPart = doc.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                List<Contact> contacts = new List<Contact>();
                Contact contact = null;
                string colNameRef = "", colEmailRef = "", colPhoneRef = "", colCompanyRef = "";
                int iterator = 0;

                string[][] headersXlsx = new string[4][];
                headersXlsx[(int)ColumnEnum.Name] = new string[]
                {
                    "Name",
                    "Namn",
                    "FullName",
                    "Full Name",
                };

                headersXlsx[(int)ColumnEnum.Email] = new string[]
                {
                    "Email",
                    "Mail",
                    "Email Address",
                    "Mail Address",
                };

                headersXlsx[(int)ColumnEnum.Phone] = new string[]
                {
                    "Phone",
                    "Telephone",
                    "Cell phone",
                    "Telefon",
                    "Mobil",
                };

                headersXlsx[(int)ColumnEnum.Company] = new string[]
                {
                    "Company",
                    "Företag",
                };

                for (char letter = 'A'; letter < 'Z'; letter++)
                {
                    string value = GetColumnHeading(doc, letter.ToString());

                    if (headersXlsx[(int)ColumnEnum.Name].Contains(value, StringComparer.OrdinalIgnoreCase))
                    {
                        colNameRef = letter.ToString();
                    }

                    else if (headersXlsx[(int)ColumnEnum.Email].Contains(value, StringComparer.OrdinalIgnoreCase))
                    {
                        colEmailRef = letter.ToString();
                    }

                    else if (headersXlsx[(int)ColumnEnum.Phone].Contains(value, StringComparer.OrdinalIgnoreCase))
                    {
                        colPhoneRef = letter.ToString();
                    }

                    else if (headersXlsx[(int)ColumnEnum.Company].Contains(value, StringComparer.OrdinalIgnoreCase))
                    {
                        colCompanyRef = letter.ToString();
                    }
                }

                foreach (Row r in sheetData.Elements<Row>())
                {
                    contact = new Contact();
                    contact.Company = new Company();
                    iterator++;

                    foreach (Cell c in r.Elements<Cell>()
                        .Where(x => x.CellReference.Value == colNameRef + iterator.ToString()
                        || x.CellReference.Value == colEmailRef + iterator.ToString()
                        || x.CellReference.Value == colPhoneRef + iterator.ToString()
                        || x.CellReference.Value == colCompanyRef + iterator.ToString()))
                    {
                        if (c != null)
                        {
                            string value = c.InnerText;

                            if (c.DataType != null)
                            {
                                switch (c.DataType.Value)
                                {
                                    case CellValues.Boolean:
                                        break;
                                    case CellValues.Date:
                                        break;
                                    case CellValues.Error:
                                        break;
                                    case CellValues.InlineString:
                                        break;
                                    case CellValues.Number:
                                        break;

                                    case CellValues.SharedString:
                                        var sTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

                                        if (sTable != null)
                                        {
                                            value = sTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                                        }
                                        break;

                                    case CellValues.String:
                                        break;
                                    default:
                                        break;
                                }
                            }

                            if (c.CellReference.Value == colNameRef + iterator.ToString())
                            {
                                contact.Name = value;
                            }

                            else if (c.CellReference.Value == colEmailRef + iterator.ToString())
                            {
                                contact.Email = value;
                            }

                            else if (c.CellReference.Value == colPhoneRef + iterator.ToString())
                            {
                                contact.Phone = value;
                            }

                            else if (c.CellReference.Value == colCompanyRef + iterator.ToString())
                            {
                                contact.Company.CompanyName = value;
                            }
                        }
                    }

                    if (headersXlsx[(int)ColumnEnum.Name].FirstOrDefault(x => x == contact.Name) == null)
                    {
                        contacts.Add(contact);
                    }
                }

                return contacts;
            }
        }

        /// <summary>
        /// Validates the open xml file according to the Open XML standard built-in class OpenXmlValidator.
        /// </summary>
        /// <param name="stream">The file as a stream.</param>
        /// <returns>Whether the file successfully validated or not.</returns>
        public bool Validate(Stream stream)
        {
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, false))
            {
                OpenXmlValidator validator = new OpenXmlValidator();
                var errors = validator.Validate(doc);

                if (errors.Count() > 0)
                {
                    return false;
                }

                return true;
            }
        }

        #region HELPER

        /// <summary>
        /// Gets the string value of a column.
        /// First retrieves the column index. Then gets the cells in the column specified by the index which is
        /// ordered by row.
        /// Then gets the content of the first cell. If the cell is stored as a stored string,
        /// the text from the first cell of the SharedStringTablePart is returned. 
        /// Else the string value of the cell is returned.
        /// </summary>
        /// <param name="doc">The current SpreadsheetDocument in use.</param>
        /// <param name="columnIndex">Column index</param>
        /// <returns>String value of the column.</returns>
        private string GetColumnHeading(SpreadsheetDocument doc, string columnIndex)
        {
            IEnumerable<Sheet> sheets = doc.WorkbookPart.Workbook.Descendants<Sheet>();

            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return null;
            }

            WorksheetPart wSheetPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheets.First().Id);
            string colIndex = GetColumnIndex(columnIndex);

            IEnumerable<Cell> cells = wSheetPart.Worksheet.Descendants<Cell>().Where(c => string.Compare(GetColumnIndex(c.CellReference.Value), colIndex, true) == 0)
                .OrderBy(r => GetRowIndex(r.CellReference));

            if (cells.Count() == 0)
            {
                // The specified column does not exist.
                return null;
            }

            Cell headCell = cells.First();

            if (headCell.DataType != null && headCell.DataType.Value == CellValues.SharedString)
            {
                SharedStringTablePart shareStringPart = doc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
                SharedStringItem[] items = shareStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
                return items[int.Parse(headCell.CellValue.Text)].InnerText;
            }

            else
            {
                return headCell.CellValue.Text;
            }
        }

        /// <summary>
        /// Create a regular expression to match the column name portion of the cell name.
        /// </summary>
        /// <param name="cellReference">Column index portion, e.g. A.</param>
        /// <returns></returns>
        private string GetColumnIndex(string cellReference)
        {
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellReference);

            return match.Value;
        }

        /// <summary>
        /// Create a regular expression to match the row index portion of the cell name.
        /// </summary>
        /// <param name="cellReference">Row index portion, e.g. 1.</param>
        /// <returns></returns>
        private uint GetRowIndex(string cellReference)
        {
            Regex regex = new Regex(@"\d+");
            Match match = regex.Match(cellReference);

            return uint.Parse(match.Value);
        }
        #endregion
    }
}
